﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WFCSharp_II
{
    public partial class ManipulateData : Form
    {
        private DataTable orders;
        private SqlDataAdapter sqlDataAdapter;

        public ManipulateData()
        {
            InitializeComponent();
        }

        private void getDataButton_Click(object sender, EventArgs e)
        {
            orders = GetOrder();

            // Tạo chuỗi địa chỉ đầy đủ
            DataColumn countDay = new DataColumn("ShipTo", typeof(string));

            countDay.Expression = "ShipName + ', ' + ShipAddress + ', ' + ShipCity + ', ' + ShipCountry";
            orders.Columns.Add(countDay);

            // Tạo cột số thứ tự tăng tự động
            DataColumn autoColumn = new DataColumn("AutoID", typeof(int));

            autoColumn.AutoIncrement = true;
            autoColumn.AutoIncrementSeed = 1;
            autoColumn.AutoIncrementStep = 1;
            orders.Columns.Add(autoColumn);

            dataGridView.DataSource = orders;

            DataView deletedView = new DataView(orders);

            deletedView.RowStateFilter = DataViewRowState.Deleted;
            deletedDataGridView.DataSource = deletedView;
        }

        private DataTable GetOrder()
        {
            // Load dữ liệu của bảng Northwind.Orders lên lưới dùng DataTable
            // Lấy các cột OrderID, ShipName, ShipAddress, ShipCity, ShipCountry
            DataTable orderTable = new DataTable();

            using (SqlConnection sqlConnection = new SqlConnection())
            {
                // Chuỗi kết nối
                sqlConnection.ConnectionString =
                    "Data Source=dell-n5110;Initial Catalog=Northwind; Integrated Security=true;";

                // Mở kết nối trước khi dùng
                sqlConnection.Open();

                // Câu SELECT lấy một số cột của Orders
                SqlCommand selectCommand = new SqlCommand(
                    "SELECT TOP 10 OrderID, ShipName, ShipAddress, ShipCity, ShipCountry FROM Orders", sqlConnection);
                sqlDataAdapter = new SqlDataAdapter(selectCommand);

                SqlCommandBuilder commandBuilder = new SqlCommandBuilder(sqlDataAdapter);

                sqlDataAdapter.InsertCommand = commandBuilder.GetInsertCommand();
                sqlDataAdapter.UpdateCommand = commandBuilder.GetUpdateCommand();
                sqlDataAdapter.DeleteCommand = commandBuilder.GetDeleteCommand();

                // Lấy dữ liệu từ DB -> orderTable
                sqlDataAdapter.Fill(orderTable);
            }

            return orderTable;
        }

        private void dataGridView_SelectionChanged(object sender, EventArgs e)
        {
            if (dataGridView.SelectedRows.Count > 0)
            {
                DataRow currentRow = ((DataRowView)dataGridView.CurrentRow.DataBoundItem).Row;
                rowSateLabel.Text = "RowState = " + currentRow.RowState.ToString();
            }
        }

        private void acceptChangeButton_Click(object sender, EventArgs e)
        {
            orders.AcceptChanges();
        }

        private void rejectChangeButton_Click(object sender, EventArgs e)
        {
            orders.Rows[1].BeginEdit();
            orders.Rows[1][1] = "Test";
            orders.Rows[1].EndEdit();

            orders.AcceptChanges();
        }

        private void updateButton_Click(object sender, EventArgs e)
        {
            sqlDataAdapter.Update(orders);
        }
    }
}